from django.db import connection


class select_echarts_data():
    def select_yf_data(self, year=None, month=None, dept=None):
        """查议题月份数据"""
        month_sql = """ SELECT MONTH(create_time) FROM administrative_topics a
                         """
        select_sql = """select DATE_FORMAT(a.create_time,'%c') yf, COUNT(*) cou 
        from administrative_topics a
        inner JOIN tb_users_message b ON a.employee_id=b.id 
        inner join administrative_company c on b.company_id = c.id
        where state='done' 
         """
        if year:
            select_sql += """ AND YEAR(a.create_time) = %(year)s """ % {"year": year}
            month_sql += """ where YEAR(create_time) = %(year)s """ % {"year": year}
        else:
            select_sql += """ AND YEAR(a.create_time) = YEAR(now()) """
        if month and month != '请选择月份':
            select_sql += """ AND MONTH(a.create_time) = %(month)s """ % {"month": month}
        if dept and dept != '请选择部门':
            select_sql += """ AND c.id = %(dept)s """ % {"dept": dept}

        select_sql += """ group by DATE_FORMAT(a.create_time,'%c') """
        month_sql += """ group by MONTH(create_time) """
        yf_list, data_list = ['请选择月份'], []
        with connection.cursor() as cursor:
            cursor.execute(select_sql)
            # print(select_sql)
            all_data = cursor.fetchall()
            for i in all_data:
                # yf_list.append(str(i[0]))
                data_list.append(i[1])

        with connection.cursor() as cursor:
            cursor.execute(month_sql)
            month_data = cursor.fetchall()
            for i in month_data:
                yf_list.append(str(i[0]))

        fl_data = [
            ['product', '院长办公会', '党委会', '行政例会']
        ]
        select_sql1 = """
        SELECT month, SUM(num1) as num1,SUM(num2) as num2,SUM(num3) as num3
        FROM (SELECT month, CASE WHEN type = 'yb' THEN IFNULL(num,0) ELSE 0 end as num1, CASE WHEN type = 'db' THEN IFNULL(num,0) ELSE 0 end as num2, CASE WHEN type = 'xz' THEN 					IFNULL(num,0)	ELSE 0 end as num3
        from (SELECT MONTH(a.create_time) as month,a.type,COUNT(a.type) as num FROM administrative_topics a inner JOIN tb_users_message b ON a.employee_id=b.id 
        inner join administrative_company c on b.company_id = c.id
        WHERE state='done' 
        """
        if year:
            select_sql1 += """ AND YEAR(a.create_time) = %(year)s """ % {"year": year}
        else:
            select_sql1 += """ AND YEAR(a.create_time) = YEAR(now()) """
        if month and month != '请选择月份':
            select_sql1 += """ AND MONTH(a.create_time) = %(month)s """ % {"month": month}
        if dept and dept != '请选择部门':
            select_sql1 += """ AND c.id = %(dept)s """ % {"dept": dept}

        select_sql1 += """ GROUP BY MONTH(create_time), type) base) b
        GROUP BY month """
        with connection.cursor() as cursor:
            # print(select_sql1)
            cursor.execute(select_sql1)
            sel_data = cursor.fetchall()
            for sel in sel_data:
                fl_data.append([sel[0], int(sel[1]), int(sel[2]), int(sel[3])])
        # for t in yf_list:
        #     select_sql1 = f"SELECT type, COUNT(*) FROM administrative_topics WHERE MONTH(create_time)={t.replace('月', '')} AND state='done' GROUP BY type"
        #     with connection.cursor() as cursor:
        #         cursor.execute(select_sql1)
        #         sel_data = cursor.fetchall()
        #         yb_count, db_count, xz_count = 0, 0, 0
        #         for j in sel_data:
        #             if j[0] == 'yb':
        #                 yb_count = j[1]
        #             if j[0] == 'db':
        #                 db_count = j[1]
        #             if j[0] == 'xz':
        #                 xz_count = j[1]
        #         fl_data.append([t, yb_count, db_count, xz_count])

        select_sql2 = """SELECT c.name dep, COUNT(a.id), c.id 
                        FROM administrative_topics a 
                        inner JOIN tb_users_message b ON a.employee_id=b.id 
                        inner join administrative_company c on b.company_id = c.id
                        WHERE A.state='done' """
        if year:
            select_sql2 += """ AND YEAR(a.create_time) = %(year)s """ % {"year": year}
        else:
            select_sql2 += """ AND YEAR(a.create_time) = YEAR(now()) """
        if month and month != '请选择月份':
            select_sql2 += """ AND MONTH(a.create_time) = %(month)s """ % {"month": month}
        if dept and dept != '请选择部门':
            select_sql2 += """ AND c.id = %(dept)s """ % {"dept": dept}

        select_sql2 += """ GROUP BY c.name,c.id """
        with connection.cursor() as cursor:
            cursor.execute(select_sql2)
            all_data2 = cursor.fetchall()
            dep_list, dep_number_list, dep_dict_list = [], [], [{"name": '请选择部门', "value": '请选择部门'}]
            for tt in all_data2:
                dep_list.append(tt[0])
                dep_number_list.append(tt[1])
                dep_dict_list.append({"name": tt[0], "value": tt[2]})

        select_sql3 = "SELECT DISTINCT(YEAR(create_time)) years FROM `administrative_topics`"
        with connection.cursor() as cursor:
            cursor.execute(select_sql3)
            all_data3 = cursor.fetchall()
            year_list = []
            for i in all_data3:
                year_list.append(i[0])
        return yf_list, data_list, fl_data, dep_list, dep_number_list, year_list, dep_dict_list

    def car_data(self, start_time, end_time):
        """车辆报表数据"""
        select_sql = "SELECT DISTINCT(MONTH(start_time)) start_month FROM `administrative_vehicleapply` WHERE state='cgb'"
        select_sql1 = "SELECT vehicles, COUNT(*) FROM `administrative_vehicleapply` WHERE MONTH(start_time)={} AND state='cgb' GROUP BY vehicles"
        select_sql3 = "SELECT drivers, COUNT(*) FROM administrative_vehicleapply WHERE state='cgb' GROUP BY drivers"
        select_sql4 = "SELECT B.department dep, COUNT(*) FROM (SELECT * FROM `administrative_vehicleapply` WHERE state='cgb') A LEFT JOIN tb_users_message B ON A.apply_user_id=B.id GROUP BY dep"
        if start_time and end_time:
            select_sql = f"SELECT DISTINCT(MONTH(start_time)) start_month FROM `administrative_vehicleapply` WHERE state='cgb' AND start_time BETWEEN '{start_time}' AND '{end_time}'"
            select_sql1 = "SELECT vehicles, COUNT(*) FROM `administrative_vehicleapply` WHERE MONTH(start_time)={} AND state='cgb' GROUP BY vehicles"
            select_sql3 = f"SELECT drivers, COUNT(*) FROM administrative_vehicleapply WHERE state='cgb' AND start_time BETWEEN '{start_time}' AND '{end_time}' GROUP BY drivers"
            select_sql4 = f"SELECT B.department dep, COUNT(*) FROM (SELECT * FROM `administrative_vehicleapply` WHERE state='cgb' AND start_time BETWEEN '{start_time}' AND '{end_time}') A LEFT JOIN tb_users_message B ON A.apply_user_id=B.id GROUP BY dep"

        with connection.cursor() as cursor:
            cursor.execute(select_sql)
            all_data = cursor.fetchall()
            yf_list, data_list = [], []
            fl_data = [
                ['product', '鄂H5396A', '鄂H5395A', '鄂A9U525']
            ]
            for i in all_data:
                yf_list.append(str(i[0])+"月")

                with connection.cursor() as cursor:
                    cursor.execute(select_sql1.format(i[0]))
                    all_data1 = cursor.fetchall()
                    yb_count, db_count, xz_count = 0, 0, 0
                    for j in all_data1:
                        if j[0] == "鄂H5396A(7座)":
                            yb_count = j[1]
                        if j[0] == "鄂H5395A(5座)":
                            db_count = j[1]
                        if j[0] == "鄂A9U525(7座)":
                            xz_count = j[1]
                    fl_data.append([str(i[0])+"月", yb_count, db_count, xz_count])

        # 司机出车
        with connection.cursor() as cursor:
            cursor.execute(select_sql3)
            all_data3 = cursor.fetchall()
            hyl_count, hdc_count, lwj_count, mz_count = 0, 0, 0, 0
            for t in all_data3:
                if t[0] == "胡友林":
                    hyl_count = t[1]
                elif t[0] == "胡道春":
                    hdc_count = t[1]
                elif t[0] == "刘文军":
                    lwj_count = t[1]
                else:
                    mz_count = t[1]

        # 申请科室
        with connection.cursor() as cursor:
            cursor.execute(select_sql4)
            all_data4 = cursor.fetchall()
            dep_list, dep_number_list, dep_dict_list = [], [], []
            for tt in all_data4:
                dep_list.append(tt[0])
                dep_number_list.append(tt[1])
                # dep_dict_list.append({"name": tt[0], "value": tt[2]})

        return fl_data, hyl_count, hdc_count, lwj_count, mz_count, dep_list, dep_number_list